'use strict';
const BaseService = require('./base');
const ResponseUtil = require('../utils/ResponseUtil');
const HelperUtil = require('../utils/HelperUtil');
const VRCE = require('../utils/ViewResultCodeEnum');
const RCEM = require('../utils/ResultCodeEnumMsg');
const StringUtil = require('../utils/StringUtil');
const CryptoUtil = require('../utils/CryptoUtil');
const Constant = require('../config/Constant');
const moment = require('moment');
const OperatorType = require('../config/OperatorType');

class SysUserService extends BaseService {


    async selectUserById(userId) {
        let sql = `
                     select u.tenant_id,u.city,u.role_key,u.user_id, u.dept_id, u.user_name, u.nick_name, u.email, u.avatar,u.qr_code, u.phone, u.password, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, u.create_by_id,
                     u.update_by, u.update_by_id, u.update_time, u.password_status, u.password_time
                     from sys_user u
                     left join sys_user_role ur on u.user_id = ur.user_id
                     left join sys_role r on r.role_id = ur.role_id where u.user_id=? 
                  `;

        let result = await this.executeSqlFirst(sql, [userId]);
        if (result) {

            let sql = `
                    select 
                     r.role_id, r.role_name, r.role_key, r.role_sort, r.status as role_status,r.status
                     from sys_user u
                     inner join sys_user_role ur on u.user_id = ur.user_id
                     inner join sys_role r on r.role_id = ur.role_id where u.user_id=? 
                      `;
            let roles = await this.executeSql(sql, [userId]);

            for (let i = 0; i < roles.length; i++) {
                if (roles[i].roleId == 1) {
                    roles[i].admin = true;
                } else {
                    roles[i].admin = false;
                }
            }

            let isAdmin = this.ctx.service.permission.isAdmin(roles);

            result.admin = isAdmin;
            result.roles = roles;

        }
        return result;
    }

    async validateCaptcha(code, uuid) {
        let verifyKey = Constant.CAPTCHA_CODE_KEY + uuid;
        let captcha = await this.ctx.service.redis.get(verifyKey);
        await this.ctx.service.redis.del(verifyKey);
        let resp = {
            code: 200,
            msg: ''
        };
        if (!captcha) {
            resp.code = VRCE.JCAPTCHA_EXPIRE[0];
            resp.msg = VRCE.JCAPTCHA_EXPIRE[1];
            return resp;
        }
        if (code.toLowerCase() != captcha.toLowerCase()) {
            resp.code = VRCE.JCAPTCHA_ERROR[0];
            resp.msg = VRCE.JCAPTCHA_ERROR[1];
            return resp;
        }
        return resp;
    }

    async selectLoginUser(loginName) {
        let sql = `
                     select u.user_id,u.tenant_id, u.dept_id, u.user_name, u.nick_name, u.email, u.avatar, u.phone, u.password, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, u.create_by_id,
                     u.update_by, u.update_by_id, u.update_time, u.password_status, u.password_time
                     from sys_user u
                     left join sys_user_role ur on u.user_id = ur.user_id
                     left join sys_role r on r.role_id = ur.role_id where (u.phone=? or u.email=?) 
                  `;

        let result = await this.executeSqlFirst(sql, [loginName, loginName]);
        if (result) {
            let sql = `
                    select 
                     r.role_id, r.role_name, r.role_key, r.role_sort, r.status as role_status
                     from sys_user u
                     left join sys_user_role ur on u.user_id = ur.user_id
                     left join sys_role r on r.role_id = ur.role_id where u.user_id=? 
                      `;
            let roles = await this.executeSql(sql, [result.userId]);
            result.roles = roles;
        }
        return result;
    }

    async recordLoginInfo(userId) {
        let sql = 'update sys_user set login_ip=?,login_date=? where user_id=? ';
        let size = await this.executeSqlSucc(sql, [this.ctx.getIP(), new Date(), userId]);
        return size;
    }

    async login({code, loginName, password, uuid}) {
        if (!code || !loginName || !password || !uuid) {
            return ResponseUtil.retArgErr();
        }

        let verResp = await this.validateCaptcha(code, uuid);
        if (verResp.code != 200) {
            return ResponseUtil.resError(verResp.msg, verResp.code);
        }

        let user = await this.selectLoginUser(loginName);

        if (null == user) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_NOT_EXIST[1], VRCE.ADMIN_USER_NOT_EXIST[0]);
        }

        let matcheResult = await HelperUtil.matchesPassword(password, user.password);
        if (matcheResult != true) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_PASSWORD_ERROR[1], VRCE.ADMIN_USER_PASSWORD_ERROR[0]);
        }

        if (user.delFlag == 1) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_DELED[1], VRCE.ADMIN_USER_DELED[0]);
        }

        if (user.status == 1) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_INVALID[1], VRCE.ADMIN_USER_INVALID[0]);
        }

        let sql = 'select * from sys_tenant where id=?';
        console.log('tenantId===',user)
        const tenant = await this.executeSqlFirst(sql,[user.tenantId]);
        if(!tenant){
            return ResponseUtil.resError('未关联租户，请联系管理员');
        }

        if(tenant.status != 0){
            return ResponseUtil.resError('该账户关联的租户已被禁用');
        }

        await this.recordLoginInfo(user.userId);


        //4.创建token
        let uToken = CryptoUtil.MD5(user.userId+'');//StringUtil.getUUIDV2();

        let tokenParams = {
            [Constant.LOGIN_USER_KEY]: uToken,
            email: user.email,
            name: user.userName,
            id: user['userId'],
            nickName: user['nickName']
        };

        this.ctx.app.logger.info('user roles:', JSON.stringify(user.roles));
        let isAdmin = this.ctx.service.permission.isAdmin(user.roles);

        this.ctx.app.logger.info('user isAdmin:', isAdmin);

        const permissions = await this.ctx.service.permission.getMenuPermission(user);

        //5.用户信息存redis
        let redisParams = {
            accountNonExpired: true,//账户是否未过期,过期无法验证
            accountNonLocked: true,//指定用户是否解锁,锁定的用户无法进行身份验证
            admin: isAdmin,//是否管理员
            appToken: false,//是否appToken
            accountType:user.accountType,
            tenantId:user.tenantId,
            authorities: [],//
            credentialsNonExpired: true,//指示是否已过期的用户的凭据(密码),过期的凭据防止认证
            demoAccount: user.demoAccount ? true : false,//是否演示账号
            enabled: true,//用户状态
            expireTime: new Date(moment().add(Constant.cmsExpireTime, 'minutes')).getTime(),//过期时间
            lastRefreshTime: new Date().getTime(),//最新刷新token时间
            ipaddr: this.ctx.getIP(),
            loginLocation: this.ctx.getIpLocal(),
            loginTime: new Date().getTime(),
            permissions: permissions,
            token: uToken,
            type: OperatorType.MANAGE,
            "user": {
                "admin": false,
                "query": {}
            },
            userId: user.userId,
            username: user.name ? user.name : user.nickName
        };


        await this.ctx.service.redis.set(Constant.LOGIN_TOKEN_KEY + uToken, JSON.stringify(redisParams), Constant.cmsExpireTime * 60);

        return this.ctx.createToken(tokenParams);
    }


    async logout(){
        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let isAdmin = loginUserInfo.isAdmin;

        let uToken = CryptoUtil.MD5(operatorId+'');

        await this.ctx.service.redis.del(Constant.LOGIN_TOKEN_KEY + uToken);
        return 1;
    }


    async updatePwd({newPassword, oldPassword}) {
        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        if (!loginUserInfo || !operatorType || !operatorId) {
            let res = ResponseUtil.resError(RCEM.NO_PERMISSION[1], RCEM.NO_PERMISSION[0]);
            return this.ctx.body = ResponseUtil.success(res);
        }

        let user = await this.selectUserById(operatorId);

        let matcheResult = await HelperUtil.matchesPassword(oldPassword, user.password);
        if (matcheResult != true) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_OLDPASSWORD_ERROR[1], VRCE.ADMIN_USER_OLDPASSWORD_ERROR[0]);
        }

        let matcheResult2 = await HelperUtil.matchesPassword(newPassword, user.password);
        if (matcheResult2 == true) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_NEWPASSWORD_ERROR[1], VRCE.ADMIN_USER_NEWPASSWORD_ERROR[0]);
        }

        let newPasswordBcr = await HelperUtil.bcryptHashPassword(newPassword);
        let sql = 'update sys_user set password=? where user_id=? ';
        let size = await this.executeSqlSucc(sql, [newPasswordBcr, operatorId]);

        if (size <= 0) {
            return ResponseUtil.resError(VRCE.ADMIN_UPDATE_PWD_ERROR[1], VRCE.ADMIN_UPDATE_PWD_ERROR[0]);
        }
        return size;
    }

    async getOperatorList({keyword, pageNum, pageSize}) {
        let sql = `SELECT SQL_CALC_FOUND_ROWS * FROM sys_user where del_flag=0 `;


        let options = [];

        if (keyword) {
            sql += ` and (user_name like "%"?"%" or email like "%"?"%" or phone like "%"?"%" )`;
            options.push(keyword);
            options.push(keyword);
            options.push(keyword);
        }

        sql = this.addMysqlPageSize(sql, pageNum, pageSize);
        let result = await this.queryPageData(sql, options);

        let list = result.list;
        await Promise.all(list.map(async (item) => {
            sql = 'SELECT a.dept_id,b.`name` as dept_name FROM sys_dept_user_relevant a INNER JOIN sys_dept b on a.dept_id = b.id where a.valid=1 AND a.is_leader = 0 and a.user_id=? ';
            let depts = await this.executeSql(sql, [item.userId]);
            item.depts = depts;
        }));
        return result;
    }

    async selectUserList({keyword, beginTime, endTime, pageNum, pageSize, phone, status, userName, delFlag, noSelectCount, email, roleKey}) {
        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let isAdmin = loginUserInfo.isAdmin;
        let tenantId = loginUserInfo.tenantId;


        let sql_list = `select u.user_id, u.dept_id,u.role_key, u.nick_name, u.user_name, u.email, u.avatar,u.qr_code, u.phone, u.password, u.sex, u.status, u.del_flag, u.login_ip, u.login_date, u.create_by, u.create_time, u.remark, u.update_by, u.update_by_id, u.update_time, u.password_status, u.password_time`
        let sql_count = `select count(*) as count `;
        let sql = ` from sys_user u where 1=1 `;

        let wheres = [
            {column: 'tenant_id', value: tenantId, options: {tname: 'u'}},
            {column: 'phone', value: phone, options: {tname: 'u'}},
            {column: 'user_name', value: userName, options: {tname: 'u', condition: 'like'}},
            {column: 'email', value: email, options: {tname: 'u', condition: 'like'}},
            {column: 'role_key', value: roleKey, options: {tname: 'u'}},
            {column: 'status', value: status, options: {tname: 'u'}},
            {column: 'create_time', value: beginTime, options: {tname: 'u', condition: '>=', format: true}},
            {column: 'create_time', value: endTime, options: {tname: 'u', condition: '<', format: true}},
            {column: 'del_flag', value: delFlag, options: {tname: 'u'}}
        ];

        let formatSqlResult = this.formatSqlWhere(sql, wheres);

        sql = formatSqlResult[0];

        let options = formatSqlResult[1];

        if (keyword) {
            sql += ` AND (u.user_name like "%"?"%" or u.phone like "%"?"%" ) `;
            options.push(keyword);
            options.push(keyword);
        }


        sql_count += sql;

        sql += ` order by u.create_time desc `;

        sql_list += sql;

        if (noSelectCount != true) {
            sql_list = this.addMysqlPageSize(sql_list, pageNum, pageSize);
        }

        console.log('sql_list===', sql_list)

        let list = await this.executeSql(sql_list, options);

        let countRet = {
            count: 0
        };
        if (noSelectCount != true) {
            countRet = await this.executeSqlFirst(sql_count, formatSqlResult[1]);
        }

        await Promise.all(list.map(async (item) => {
            let sql = `
                    select 
                     r.role_id, r.role_name, r.role_key, r.role_sort, r.status as role_status
                     from sys_user u
                     inner join sys_user_role ur on u.user_id = ur.user_id
                     inner join sys_role r on r.role_id = ur.role_id where u.user_id=? 
                      `;
            let roles = await this.executeSql(sql, [item.userId]);
            item.roles = roles;


        }));

        return {list: list, total: countRet.count};

    }

    async checkPhoneUnique(user) {
        if (!user.phone) user.phone = null;
        let userId = user.userId || 0;
        let sql = `select user_id, phone from sys_user where phone = ? `;
        let info = await this.executeSqlFirst(sql, [user.phone]);
        if (info != null && info.userId != userId) {
            return Constant.NOT_UNIQUE;
        }
        return Constant.UNIQUE;
    }


    async checkEmailUnique(user) {
        if (!user.email) user.email = null;
        let userId = user.userId || 0;
        let sql = `select user_id, email from sys_user where email = ? `;
        let info = await this.executeSqlFirst(sql, [user.email]);
        if (info != null && info.userId != userId) {
            return Constant.NOT_UNIQUE;
        }
        return Constant.UNIQUE;
    }


    async insertUser(user) {

        if (Constant.NOT_UNIQUE == (await this.checkPhoneUnique(user))) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_PHONE_EXIST[1], VRCE.ADMIN_USER_PHONE_EXIST[0]);
        }
        if (Constant.NOT_UNIQUE == (await this.checkEmailUnique(user))) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_EMAIL_EXIST[1], VRCE.ADMIN_USER_EMAIL_EXIST[0]);
        }

        if (!user.userName || !user.password) {
            return ResponseUtil.retArgErr();
        }

        if (!user.phone && !user.email) {
            return ResponseUtil.retArgErr();
        }


        let conn = await this.app.mysql.beginTransaction();

        try {
            let loginUserInfo = this.ctx.getLoginInfo();
            let operatorType = loginUserInfo.operatorType;
            let operatorId = loginUserInfo.operatorId;
            let createBy = loginUserInfo.createBy;
            let tenantId = loginUserInfo.tenantId;

            let roleIdList = user.roleIdList;


            let userParams = {
                tenant_id:tenantId,
                avatar:user.avatar,
                qr_code:user.qrCode,
                role_key: user.roleKey,
                user_no: StringUtil.getUUID(),
                user_name: user.userName,
                nick_name: user.userName,
                phone: user.phone,
                email: user.email,
                password: (await HelperUtil.bcryptHashPassword(user.password)),
                status: user.status || 0,
                del_flag: 0,
                remark: user.remark,
                city: user.city,
                create_by: createBy,
                create_by_id: operatorId,
                create_time: new Date(),
                update_time: new Date()
            };

            let insertUserResult = await conn.insert('sys_user', userParams);

            if (!insertUserResult.affectedRows) {
                throw Error('insert user error');
            }

            let userId = insertUserResult.insertId;

            if (roleIdList && Array.isArray(roleIdList) && roleIdList.length > 0) {
                for (let i = 0; i < roleIdList.length; i++) {
                    let sql = 'select * from sys_role where role_id = ?';
                    let roleInfo = await this.executeSqlFirst(sql,[roleIdList[i]]);
                    if(!roleInfo){
                        throw Error('未找到角色');
                    }
                    if(roleInfo.tenantId != tenantId){
                        throw Error('无权限操作角色');
                    }

                    let params = {
                        role_id: roleIdList[i],
                        user_id: userId
                    };
                    let insertUserRoleResult = await conn.insert('sys_user_role', params);
                    if (!insertUserRoleResult.affectedRows) {
                        throw Error('insert user role error');
                        break;
                    }
                }
            }

            await conn.commit();
            return userId;

        } catch (e) {
            this.ctx.app.logger.error('insertUser error', e.message);
            await conn.rollback();
            throw Error(e.message);
        }
    }


    async deleteUserRoleByUserId(userId, conn) {
        let sql = `delete from sys_user_role where user_id=? `;
        let size = await this.executeSqlSucc(sql, [userId], conn);
        return size;
    }

    async clearUserToken(userId){
        let uToken = CryptoUtil.MD5(userId+'');
        await this.ctx.service.redis.del(Constant.LOGIN_TOKEN_KEY + uToken);
        return true;
    }


    async updateUser(user) {
        if (Constant.NOT_UNIQUE == (await this.checkPhoneUnique(user))) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_PHONE_EXIST[1], VRCE.ADMIN_USER_PHONE_EXIST[0]);
        }
        if (Constant.NOT_UNIQUE == (await this.checkEmailUnique(user))) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_EMAIL_EXIST[1], VRCE.ADMIN_USER_EMAIL_EXIST[0]);
        }

        let object = await this.selectUserById(user.userId);
        if(!object){
            return ResponseUtil.resError('员工不存在');
        }

        let oldRoles = object.roles;
        let oldRoleIds = [];
        oldRoles.map(function (item) {
            oldRoleIds.push(item.roleId);
        });
        oldRoleIds = oldRoleIds.sort(function (a, b) {
            return a - b;
        });

        let conn = await this.app.mysql.beginTransaction();

        try {

            let loginUserInfo = this.ctx.getLoginInfo();
            let operatorType = loginUserInfo.operatorType;
            let operatorId = loginUserInfo.operatorId;
            let createBy = loginUserInfo.createBy;

            let roleIdList = user.roleIdList;
            roleIdList = roleIdList.sort(function (a, b) {
                return a - b;
            });

            let userParams = {
                role_key: user.roleKey,
                user_name: user.userName,
                nick_name: user.userName,
                avatar:user.avatar,
                qr_code:user.qrCode,
                phone: user.phone,
                email: user.email,
                status: user.status || 0,
                remark: user.remark,
                city: user.city,
                update_by: createBy,
                update_by_id: operatorId,
                update_time: new Date()
            };
            if(object.roleKey){
                delete userParams.role_key
            }
            const options = {
                where: {
                    user_id: user.userId
                }
            };
            let updateUserResult = await conn.update('sys_user', userParams, options);

            if (!updateUserResult.affectedRows) {
                throw Error('update user error');
            }
            let isClear = false;

            if (roleIdList.toString() != oldRoleIds.toString()) {

                await this.deleteUserRoleByUserId(user.userId, conn);

                if (roleIdList && Array.isArray(roleIdList) && roleIdList.length > 0) {
                    for (let i = 0; i < roleIdList.length; i++) {
                        let params = {
                            role_id: roleIdList[i],
                            user_id: user.userId
                        };
                        let insertUserRoleResult = await conn.insert('sys_user_role', params);
                        if (!insertUserRoleResult.affectedRows) {
                            throw Error('insert user role error');
                            break;
                        }
                    }

                    await this.clearUserToken(user.userId);
                    isClear = true;
                }
            }

            if(user.status !=0 && object.status != user.status && !isClear){
                await this.clearUserToken(user.userId);
            }

            await conn.commit();
            return user.userId;

        } catch (e) {
            this.ctx.app.logger.error('updateUser error', e.message);
            await conn.rollback();
            throw Error(e.message);
        }
    }


    async deleteUserById(userId) {
        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let tenantId = loginUserInfo.tenantId;

        if (userId == operatorId) {
            return ResponseUtil.resError(VRCE.ADMIN_USER_DEL_ERROR[1], VRCE.ADMIN_USER_DEL_ERROR[0]);
        }

        const userObj = await this.selectUserById(userId);
        if(!userObj){
            return ResponseUtil.resError('未找到用户');
        }
        if(userObj.tenantId != tenantId){
            return ResponseUtil.resError('无权限操作');
        }


        let conn = await this.app.mysql.beginTransaction();

        try {

            const delSize = await this.deleteUserRoleByUserId(userId, conn);

            /*  if (delSize <= 0) {
                  throw Error('delete user role error');
              }*/

            let sql = `update sys_user
		set del_flag    = 1,
			phone       = null,
			email       = null
		where user_id = ?`;

            let delSize2 = await this.executeSqlSucc(sql, [userId], conn);

            if (delSize2 <= 0) {
                throw Error('delete user error');
            }


            await conn.commit();
            return userId;
        } catch (e) {
            this.ctx.app.logger.error('deleteUserById error', e.message);
            await conn.rollback();
            throw Error(e.message);
        }
    }


    async resetPwd({userId, password}) {
        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let tenantId = loginUserInfo.tenantId;

        const userObj = await this.selectUserById(userId);
        if(!userObj){
            return ResponseUtil.resError('未找到用户');
        }
        if(userObj.tenantId != tenantId){
            return ResponseUtil.resError('无权限操作');
        }


        let sql = 'update sys_user set password=?,update_by=?,update_by_id=?,password_status=? where user_id=? ';
        let d = await HelperUtil.bcryptHashPassword(password);
        let size = await this.executeSqlSucc(sql, [d, createBy, operatorId, 0, userId]);
        await this.clearUserToken(userId);
        return size;
    }


    async selectUserEmpByRoleKey(roleKey) {
        let sql = 'select user_id as id,user_name as `name` from sys_user where `status`=0 and del_flag=0 and role_key=? ';
        let result = await this.executeSql(sql, [roleKey]);
        return result;
    }

    async updateDept() {
        let {userId, deptIds} = this.ctx.request.body;
        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let isAdmin = loginUserInfo.isAdmin;

        if (!isAdmin) {
            //该用户属于哪些部门
            let sql = 'select dept_id from sys_dept_user_relevant where user_id=? and valid=?';
            let userDepts = await this.executeSql(sql, [userId, 1]);

            //当前管理账号下的部门
            sql = 'select dept_id from sys_dept_user_relevant where user_id=? and valid=?';
            let operDepts = await this.executeSql(sql, [operatorId, 1]);

            let arr0 = [];
            userDepts.map(function (item) {
                arr0.push(item.deptId)
            });
            let arr1 = [];
            operDepts.map(function (item) {
                arr1.push(item.deptId)
            });

            let has = StringUtil.hasCommonValue(arr0, arr1);
            if (!has) {
                return ResponseUtil.resError('无权限操作');
            }
        }

        let conn = await this.app.mysql.beginTransaction();
        try {
            if (Array.isArray(deptIds)) {
                let sql = 'select * from sys_dept_user_relevant where user_id=? and valid=? ';
                let oldDeptList = await this.executeSql(sql, [userId, 1], conn);
                let delIds = [];
                for (let i = 0; i < oldDeptList.length; i++) {
                    if (deptIds.includes(oldDeptList[i].deptId)) {
                        delIds.push(oldDeptList[i].id);
                    }
                }
                for (let i = 0; i < deptIds.length; i++) {

                    let sql = 'select id from sys_dept_user_relevant where user_id=? and dept_id=? and valid=?';
                    let exist = await this.executeSqlFirst(sql, [userId, deptIds[i], 1], conn);
                    if (!exist) {
                        let params = {
                            dept_id: deptIds[i],
                            user_id: userId,
                            is_leader: 0,
                            valid: 1,
                            create_by: createBy,
                            create_by_id: operatorId
                        };
                        let insertResult = await this.executeInsert('sys_dept_user_relevant', params, conn);
                        if (!insertResult.affectedRows) {
                            throw Error('添加部门关系失败')
                        }
                    }

                }
            }
            await conn.commit();
            return userId;
        } catch (e) {
            this.ctx.app.logger.error('updateDept error', e.message);
            await conn.rollback();
            throw Error(e.message);
        }
    }


    async moveUser() {
        //是否存在部门
        //解除员工与部门的关系
        //判断该员工移除后是否存在其他部门
        //存在：不解除 员工与线索的关系
        //不存在：解除员工与线索的关系
        //移除日志记录

        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let isAdmin = loginUserInfo.isAdmin;

        let {deptId, userId} = this.ctx.request.body;

        if (!deptId || !userId) {
            return ResponseUtil.resError('参数错误。');
        }

        let existDept = await this.ctx.service.sysdept.existUserInDept(deptId,[userId]);
        if(!existDept.exist){
            return ResponseUtil.resError('无组织，不可移除。');
        }

        let conn = await this.app.mysql.beginTransaction();

        try {

            let sql = 'delete from sys_dept_user_relevant where dept_id=? and user_id=? and is_leader=?';
            let delSize = await this.executeSqlSucc(sql, [deptId, userId, 0], conn);
            if (delSize <= 0) {
                throw Error('解除员工与部门关系失败')
            }

            sql = 'SELECT DISTINCT(dept_id) from sys_dept_user_relevant where is_leader=0 and user_id=? and valid=1 ';
            let depts = await this.executeSql(sql, [userId], conn);
            if (depts.length === 0) {//解除

                let sql = 'select investor_id from crm_follow_relevant where user_id=? and valid=? ';
                let list = await this.executeSql(sql, [userId, 1], conn);

                if (list.length > 0) {
                    let sql = 'update crm_follow_relevant set valid=0 where user_id=? and valid=?';
                    await this.executeSqlSucc(sql, [userId, 1], conn);
                }

                let params = {
                    user_id: userId,
                    dept_id: deptId,
                    move_thread_id: JSON.stringify(list),
                    create_by: createBy,
                    create_by_id: operatorId
                };
                let insertResult = await this.executeInsert('sys_move_user_log', params, conn);
                if (!insertResult.affectedRows) {
                    throw Error('添加日志记录关系失败')
                }

            } else {//不解除

                let params = {
                    user_id: userId,
                    dept_id: deptId,
                    move_thread_id: JSON.stringify([]),
                    create_by: createBy,
                    create_by_id: operatorId
                };
                let insertResult = await this.executeInsert('sys_move_user_log', params, conn);
                if (!insertResult.affectedRows) {
                    throw Error('添加日志记录关系失败')
                }
            }

            await conn.commit();

            return 1;

        } catch (e) {
            this.ctx.app.logger.error('moveUser error', e.message);
            await conn.rollback();
            throw Error(e.message);
        }
    }

    async relevantUserToDept() {

        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let isAdmin = loginUserInfo.isAdmin;

        let {userId, deptId} = this.ctx.request.body;
        if (!userId || !deptId) {
            return ResponseUtil.resError('参数错误。');
        }

        let sql = 'select id from sys_dept_user_relevant where dept_id=? and user_id=? and is_leader=? and valid=?';
        let exist = await this.executeSqlFirst(sql, [deptId, userId, 0, 1]);
        if (exist) {
            return ResponseUtil.resError('部门中已存在该员工。');
        }

        let conn = await this.app.mysql.beginTransaction();

        try {

            let insertUserToDeptSize = await this.ctx.service.sysdept.insertUserToDept({
                deptId: deptId,
                userId: userId
            }, conn);
            if (insertUserToDeptSize <= 0) {
                throw Error('insert dept user relevant error');
            }

            let existDept = await this.ctx.service.sysdept.existUserInDept(deptId, [userId], conn);
            if (!existDept.exist) {
                throw Error('current user not in dept ');
            }

            let syncResult = await this.ctx.service.sysdept.syncThreadToDept(userId, deptId,[], conn);
            if (!syncResult) {
                throw Error('添加部门线索关系失败')
            }

            await conn.commit();

            return 1;

        } catch (e) {
            this.ctx.app.logger.error('relevantUserToDept error', e.message);
            await conn.rollback();
            throw Error(e.message);
        }

    }


    async getSysUserListByDept() {
        let {deptId} = this.ctx.query;

        let sql = `
                    SELECT
                      a.user_id,
                      c.id as dept_id,
                      a.user_name,
                      c.\`name\` as dept_name	
                    FROM
                        sys_user a
                        INNER JOIN sys_dept_user_relevant b ON a.user_id = b.user_id
                        INNER JOIN sys_dept c ON b.dept_id = c.id 
                    WHERE
                        a.del_flag = 0 
                        AND b.valid = 1 
                        AND b.is_leader = 0
                        AND c.valid = 1
                        AND c.id = ?
                  `;
        let result = await this.executeSql(sql, [deptId]);
        return result;
    }

    async getSysUserListByLoginUser() {

        let loginUserInfo = this.ctx.getLoginInfo();
        let operatorType = loginUserInfo.operatorType;
        let operatorId = loginUserInfo.operatorId;
        let createBy = loginUserInfo.createBy;
        let isAdmin = loginUserInfo.isAdmin;

        let {deptId} = this.ctx.query;

        let deptList = [];

        if (isAdmin) {

            if(deptId){
                let sql = 'select id as dept_id,name as dept_name from sys_dept where valid=1 and id=? ';
                deptList = await this.executeSql(sql,[deptId]);
            }else{
                let sql = 'select id as dept_id,name as dept_name from sys_dept where valid=1 ';
                deptList = await this.executeSql(sql);
            }

        } else {
            if(deptId){
                let sql = 'select T1.id as dept_id,T1.name as dept_name from sys_dept as T1 where T1.valid=1 AND T1.id=? AND EXISTS (SELECT 1 FROM sys_dept_user_relevant T6 WHERE T6.valid=1 AND T6.dept_id=T1.id AND T6.user_id = ? )';
                deptList = await this.executeSql(sql, [deptId,operatorId]);
            }else{
                let sql = 'select T1.id as dept_id,T1.name as dept_name from sys_dept as T1 where T1.valid=1 AND EXISTS (SELECT 1 FROM sys_dept_user_relevant T6 WHERE T6.valid=1 AND T6.dept_id=T1.id AND T6.user_id = ? )';
                deptList = await this.executeSql(sql, [operatorId]);
            }
        }

        await Promise.all(deptList.map(async (item) => {
            let sql = 'select a.user_id, u.user_name from sys_dept_user_relevant a inner join sys_user u on a.user_id=u.user_id where a.dept_id=? and a.valid=1 and a.is_leader=0 and u.del_flag=0 ';
            let users = await this.executeSql(sql, [item.deptId]);
            item.children = users;
        }));

        return deptList;

    }



    async updateUserProfile(){
        let {userId,avatar,qrCode} = this.ctx.request.body;

        if(!userId){
            return ResponseUtil.resError('参数错误。');
        }
        let sql = 'update sys_user set avatar=?,qr_code=? where user_id=? ';
        let size = await this.executeSqlSucc(sql,[avatar,qrCode,userId]);

        return size;
    }

}

module.exports = SysUserService;
